using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public BooklkiVO getBooklki(int? idBookLki)
		{
			BooklkiVO booklkiVO = null;
			List<BooklkiVO> listBooklkiVOs = getBooklkis(idBookLki, null, null, null, null, null, null, null, null, null);
			if (listBooklkiVOs != null && listBooklkiVOs.Count > 0)
			{
				booklkiVO = listBooklkiVOs[0];
			}
			return booklkiVO;
		}

		public List<BooklkiVO> getBooklkis()
		{
			return getBooklkis(null, null, null, null, null, null, null, null, null, null);
		}

		public List<BooklkiVO> getBooklkis(int? idBookLki)
		{
			return getBooklkis(idBookLki, null, null, null, null, null, null, null, null, null);
		}

		public List<BooklkiVO> getBooklkis(int? idBookLki, int? idPlayList, string bookLki, DateTime? vigenciaIni, DateTime? vigenciaFin, bool? ativo, bool? home, bool? evento, bool? filme, bool? ensaio)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT blk.idBookLki as blk_idBookLki, blk.bookLki as blk_bookLki, blk.vigenciaIni as blk_vigenciaIni, blk.vigenciaFin as blk_vigenciaFin, blk.ativo as blk_ativo, blk.idPlayList as blk_idPlayList, Pll.playlist as Pll_playlist, blk.home as blk_home, blk.evento as blk_evento, blk.filme as blk_filme, blk.ensaio as blk_ensaio, blk.cadastro as blk_cadastro, blk.cadastradoPor as blk_cadastradoPor, blk.edicao as blk_edicao, blk.editadoPor as blk_editadoPor  ");
			query.AppendLine("FROM booklki as blk LEFT OUTER JOIN playlist as Pll on blk.idPlayList=Pll.idPlayList  ");
			if (idBookLki != null)
			{
				query.AppendLine(where + "blk.idBookLki=@idBookLki ");
				where = "AND ";
			}
			if (bookLki != null)
			{
				query.AppendLine(where + "blk.bookLki=@bookLki ");
				where = "AND ";
			}
			if (vigenciaIni != null)
			{
				query.AppendLine(where + "blk.vigenciaIni=@vigenciaIni ");
				where = "AND ";
			}
			if (vigenciaFin != null)
			{
				query.AppendLine(where + "blk.vigenciaFin=@vigenciaFin ");
				where = "AND ";
			}
			if (ativo != null)
			{
				query.AppendLine(where + "blk.ativo=@ativo ");
				where = "AND ";
			}
			if (idPlayList != null)
			{
				query.AppendLine(where + "blk.idPlayList=@idPlayList ");
				where = "AND ";
			}
			if (home != null)
			{
				query.AppendLine(where + "blk.home=@home ");
				where = "AND ";
			}
			if (evento != null)
			{
				query.AppendLine(where + "blk.evento=@evento ");
				where = "AND ";
			}
			if (filme != null)
			{
				query.AppendLine(where + "blk.filme=@filme ");
				where = "AND ";
			}
			if (ensaio != null)
			{
				query.AppendLine(where + "blk.ensaio=@ensaio ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY blk.idBookLki ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idBookLki!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookLki", idBookLki));
			if (bookLki!= null) cmd.Parameters.Add(getParameter(cmd, "@bookLki", bookLki));
			if (vigenciaIni!= null) cmd.Parameters.Add(getParameter(cmd, "@vigenciaIni", vigenciaIni));
			if (vigenciaFin!= null) cmd.Parameters.Add(getParameter(cmd, "@vigenciaFin", vigenciaFin));
			if (ativo!= null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value?"S":"N"));
			if (idPlayList!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlayList", idPlayList));
			if (home!= null) cmd.Parameters.Add(getParameter(cmd, "@home", home.Value?"S":"N"));
			if (evento!= null) cmd.Parameters.Add(getParameter(cmd, "@evento", evento.Value?"S":"N"));
			if (filme!= null) cmd.Parameters.Add(getParameter(cmd, "@filme", filme.Value?"S":"N"));
			if (ensaio!= null) cmd.Parameters.Add(getParameter(cmd, "@ensaio", ensaio.Value?"S":"N"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<BooklkiVO> listBooklki = new List<BooklkiVO>();

				while (reader.Read())
				{
					BooklkiVO booklkiVO = BooklkiVO.CreateVO();
					booklkiVO.idBookLki = reader.GetInt32(reader.GetOrdinal("blk_idBookLki"));
					booklkiVO.bookLki = reader.GetString(reader.GetOrdinal("blk_bookLki"));
					booklkiVO.vigenciaIni = reader.GetDateTime(reader.GetOrdinal("blk_vigenciaIni"));
					booklkiVO.vigenciaFin = reader.GetDateTime(reader.GetOrdinal("blk_vigenciaFin"));
					booklkiVO.ativo = reader.GetString(reader.GetOrdinal("blk_ativo")).Equals("S");
					if (!reader.IsDBNull(reader.GetOrdinal("blk_idPlayList")))
						booklkiVO.idPlayList = reader.GetInt32(reader.GetOrdinal("blk_idPlayList"));
					if (!reader.IsDBNull(reader.GetOrdinal("Pll_playlist")))
						booklkiVO.idPlayListVO.playlist = reader.GetString(reader.GetOrdinal("Pll_playlist"));
					booklkiVO.home = reader.GetString(reader.GetOrdinal("blk_home")).Equals("S");
					booklkiVO.evento = reader.GetString(reader.GetOrdinal("blk_evento")).Equals("S");
					booklkiVO.filme = reader.GetString(reader.GetOrdinal("blk_filme")).Equals("S");
					booklkiVO.ensaio = reader.GetString(reader.GetOrdinal("blk_ensaio")).Equals("S");
					booklkiVO.cadastro = reader.GetDateTime(reader.GetOrdinal("blk_cadastro"));
					booklkiVO.cadastradoPor = reader.GetString(reader.GetOrdinal("blk_cadastradoPor"));
					booklkiVO.edicao = reader.GetDateTime(reader.GetOrdinal("blk_edicao"));
					booklkiVO.editadoPor = reader.GetString(reader.GetOrdinal("blk_editadoPor"));


					listBooklki.Add(booklkiVO);
				}
				reader.Close();
				return listBooklki;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setBooklki(BooklkiVO booklkiVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE booklki ");
			query.AppendLine("SET bookLki=@bookLki, vigenciaIni=@vigenciaIni, vigenciaFin=@vigenciaFin, ativo=@ativo, idPlayList=@idPlayList, home=@home, evento=@evento, filme=@filme, ensaio=@ensaio,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idBookLki=@idBookLki ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLki", booklkiVO.idBookLki));
			cmd.Parameters.Add(getParameter(cmd, "@bookLki", booklkiVO.bookLki));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaIni", booklkiVO.vigenciaIni));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaFin", booklkiVO.vigenciaFin));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (booklkiVO.ativo==null?" ":(booklkiVO.ativo.Value?"S":"N"))));
			if (booklkiVO.idPlayList == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", booklkiVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@home", (booklkiVO.home==null?" ":(booklkiVO.home.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@evento", (booklkiVO.evento==null?" ":(booklkiVO.evento.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@filme", (booklkiVO.filme==null?" ":(booklkiVO.filme.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@ensaio", (booklkiVO.ensaio==null?" ":(booklkiVO.ensaio.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", booklkiVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", booklkiVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", booklkiVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", booklkiVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newBooklki(BooklkiVO booklkiVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO booklki ");
			query.AppendLine("  ( bookLki, vigenciaIni, vigenciaFin, ativo, idPlayList, home, evento, filme, ensaio, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @bookLki, @vigenciaIni, @vigenciaFin, @ativo, @idPlayList, @home, @evento, @filme, @ensaio, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@bookLki", booklkiVO.bookLki));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaIni", booklkiVO.vigenciaIni));
			cmd.Parameters.Add(getParameter(cmd, "@vigenciaFin", booklkiVO.vigenciaFin));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (booklkiVO.ativo==null?" ":(booklkiVO.ativo.Value?"S":"N"))));
			if (booklkiVO.idPlayList == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlayList", booklkiVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@home", (booklkiVO.home==null?" ":(booklkiVO.home.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@evento", (booklkiVO.evento==null?" ":(booklkiVO.evento.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@filme", (booklkiVO.filme==null?" ":(booklkiVO.filme.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@ensaio", (booklkiVO.ensaio==null?" ":(booklkiVO.ensaio.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", booklkiVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", booklkiVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", booklkiVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", booklkiVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('booklki') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delBooklki(BooklkiVO booklkiVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM booklki ");
			query.AppendLine("WHERE idBookLki=@idBookLki ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLki", booklkiVO.idBookLki));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountBooklkixIdPlayList(int? idPlayList)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPlayList) as ocorrencias FROM booklki WHERE IdPlayList=@IdPlayList ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPlayList", idPlayList));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
